USE [loan]
GO
/****** Object:  UserDefinedFunction [dbo].[CalculateInterest]    Script Date: 04/17/2013 14:46:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[CalInterestMnthly]
(
	@report_date DATETIME,
	@principal DECIMAL(18, 2),
	@interest_rate DECIMAL(18, 8),
	@loan_start_date DATETIME,	
	@term INTEGER,	
	@payment_amount DECIMAL(18, 2)	
)
RETURNS DECIMAL(18, 2)
AS
BEGIN

DECLARE @interest DECIMAL(18, 8) = 0, 
	@mnthly_interest DECIMAL(18, 8) = 0,
	@mnthly_interest_rate DECIMAL(18, 8) = (@interest_rate / 100) / 12,
	@daily_interest_rate DECIMAL(18, 8) = (@interest_rate / 100) / 365, 
	@payment_date DATETIME = DATEADD(month, 1, @loan_start_date), 	
	@loan_end_date DATETIME = DATEADD(month, @term, @loan_start_date),
	@num_days INTEGER,	
	@month_end DATETIME

	IF @loan_start_date < @report_date AND (@loan_end_date > @report_date OR (YEAR(@loan_end_date) = YEAR(@report_date) AND MONTH(@loan_end_date) = MONTH(@report_date)))
	BEGIN
		SET @month_end = DATEADD(day, DAY(DATEADD(mm, DATEDIFF(mm, -1, @loan_start_date), -1)) - DAY(@loan_start_date), @loan_start_date)
		
		IF YEAR(@loan_start_date) = YEAR(@report_date) AND MONTH(@loan_start_date) = MONTH(@report_date)
		BEGIN
			SET @num_days = DATEDIFF(day, @loan_start_date, @report_date)			
		END
		ELSE
		BEGIN
			SET @num_days = DATEDIFF(day, @loan_start_date, @month_end)	
		END		
		 
				
		SET @interest = @principal * @daily_interest_rate * @num_days
		SET @principal = @principal + @interest - @payment_amount 
		SET @month_end = DATEADD(month, 1, @month_end)
		
		
		WHILE (YEAR(@month_end) < YEAR(@report_date) OR (YEAR(@month_end) = YEAR(@report_date) AND MONTH(@month_end) < MONTH(@report_date)))
		BEGIN
			SET @interest = @principal * @mnthly_interest_rate			
			SET @principal = @principal + @interest - @payment_amount
			SET @month_end = DATEADD(month, 1, @month_end)
		END	
		
		IF YEAR(@month_end) = YEAR(@report_date) AND MONTH(@month_end) = MONTH(@report_date)
		BEGIN
			IF @report_date <= @loan_end_date
			BEGIN
				SET @num_days = DAY(@report_date)
			END
			ELSE
			BEGIN
				SET @num_days = DAY(@loan_end_date)
			END
			
			SET @interest = @principal * @daily_interest_rate * @num_days
		END	
	END
	RETURN @interest
END;

